﻿using System;
using System.Data;
using Npgsql;

namespace XD.Core
{
    /// <summary>
    /// 数据库管理类
    /// 直接操作数据库
    /// </summary>
    public class PostgresqlDB : IDisposable
    {
        #region 字段

        #region 连接配置

        /// <summary>
        /// 服务器地址
        /// </summary>
        private string server;

        /// <summary>
        /// 端口
        /// </summary>
        private string port;

        /// <summary>
        /// 用户名
        /// </summary>
        private string username;

        /// <summary>
        /// 密码
        /// </summary>
        private string password;
        
        /// <summary>
        /// 数据库名称
        /// </summary>
        private string db;

        #endregion

        #region 数据库操作相关变量

        /// <summary>
        /// 数据库连接器
        /// </summary>
        private NpgsqlConnection _conn;

        /// <summary>
        /// 通过连接器创建NpgsqlCommand对象，用于执行SQL语句
        /// </summary>
        private NpgsqlCommand _comm;

        /// <summary>
        /// 将数据表中获取的记录生成DataSet
        /// </summary>
        private NpgsqlDataAdapter _adapter;

        /// <summary>
        /// 创建一个事务对象
        /// </summary>
        private NpgsqlTransaction _trans;

        #endregion

        #endregion

        #region 构造函数

        private PostgresqlDB() { }

        /// <summary>
        /// 数据库管理类
        /// </summary>
        /// <param name="dbConfig">数据库配置</param>
        public PostgresqlDB(string server, string port, string username, string password, string db)
        {
            this.server = server;
            this.port = port;
            this.username = username;
            this.password = password;
            this.db = db;
        }

        ~PostgresqlDB()
        {
        }

        #endregion

        #region 公共方法

        #region 数据库操作：连接数据库、断开数据库、数据库连接状态

        /// <summary>
        /// 打开数据库连接
        /// </summary>
        /// <returns></returns>
        public bool Open()
        {
            try
            {
                //构造SQL语句
                string sql = String.Format(
                    "server={0};port={1};userid={2};password={3};database={4};pooling=true;maxpoolsize=40;timeout=5;",
                    server, port, username, password, db);

                //初始化数据库连接参数
                _conn = new NpgsqlConnection(sql);
                //打开数据库连接
                _conn.Open();
                //初始化
                _comm = _conn.CreateCommand();
                _adapter = new NpgsqlDataAdapter(_comm);
                _trans = null;
                return true;
            }
            catch (Exception ex)
            {
                if (_conn != null)
                    _conn.Close();
                _conn = null;
#if DEBUG
                System.Diagnostics.Debug.WriteLine(string.Format("#OpenDB:{0};{1}", ex.Message, ex.StackTrace));
#endif
                return false;
            }
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public void Close()
        {
            if (_conn != null)
            {
                _adapter.Dispose();
                _adapter = null;
                _comm.Dispose();
                _comm = null;
                _conn.Close();
                _conn.Dispose();
                _conn = null;
            }
        }

        /// <summary>
        /// 获取数据库的连接状态
        /// </summary>
        /// <returns>true表示已连接，false表示未连接</returns>
        public bool GetConnStatus()
        {
            return (_conn != null && _conn.State == ConnectionState.Open);
        }

        #endregion

        #region 数据表操作：查询数据表或视图是否存在

        /// <summary>
        /// 查询指定名称的数据表是否存在
        /// </summary>
        /// <param name="tableName">数据表名</param>
        /// <returns>true:存在，false:不存在</returns>
        public bool QueryTableIsExist(string tableName)
        {
            //构建SQL语句
            string sql = String.Format("select count(*) from pg_tables where tablename = '{0}';", tableName.ToLower());
            
            //执行SQL语句
            _comm.Transaction = _trans;
            _comm.CommandText = sql;
            NpgsqlDataReader readerTemp = _comm.ExecuteReader();

            //获取查询结果
            long value = -1;
            if (readerTemp.HasRows == true)
            {
                readerTemp.Read();
                value = readerTemp.GetInt64(0);
            }

            //释放资源
            readerTemp.Close();

            //value=1表示数据表存在；value=0表示数据表不存在
            if (value > 0)
            {
                return true;
            }
            return false;
        }

        /// <summary>
        /// 查询指定名称的视图是否存在
        /// </summary>
        /// <param name="viewName">视图名</param>
        /// <returns>true:存在，false:不存在</returns>
        public bool QueryViewIsExist(string viewName)
        {
            //构建SQL语句
            string sql = String.Format("select count(*) from pg_views where viewname = '{0}';", viewName.ToLower());
            
            //执行SQL语句
            _comm.Transaction = _trans;
            _comm.CommandText = sql;
            NpgsqlDataReader readerTemp = _comm.ExecuteReader();

            //获取查询结果
            long value = -1;
            if (readerTemp.HasRows == true)
            {
                readerTemp.Read();
                value = readerTemp.GetInt64(0);
            }

            //释放资源
            readerTemp.Close();

            //value=1表示数据表存在；value=0表示数据表不存在
            if (value == 0)
            {
                return false;
            }
            return true;
        }

        #endregion

        #region 执行sql语句

        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="sql"></param>
        public void ExecuteNonQuery(string sql)
        {
            try
            {
                _comm.Transaction = _trans;
                _comm.CommandText = sql;
                _comm.ExecuteNonQuery();
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable ExecuteQuery(string sql)
        {
            try
            {                
                _comm.Transaction = _trans;
                _comm.CommandText = sql;
                DataTable dt = new DataTable();
                using (NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(_comm))
                {
                    adapter.Fill(dt);
                }
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        
        #endregion

        #region 事务管理

        /// <summary>
        /// 开始执行事务
        /// </summary>
        public void BeginTransaction()
        {
            if (_conn != null)
            {
                _trans = _conn.BeginTransaction();
            }
        }

        /// <summary>
        /// 事务结束，保存并提交数据库数据，
        /// </summary>
        public void Commit()
        {
            if (_trans != null)
            {
                _trans.Commit();
            }
        }

        /// <summary>
        /// 回滚事务
        /// </summary>
        public void Rollback()
        {
            if (_trans != null)
            {
                _trans.Rollback();
            }
        }

        /// <summary>
        /// 结束事务，并释放资源
        /// </summary>
        public void EndTransaction()
        {
            if (_trans != null)
            {
                _trans.Dispose();
                _trans = null;
            }
        }

        #endregion

        #endregion

        public void Dispose()
        {
            this.Close();
        }
    }
}
